package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import object.Product;
import object.ProductDetail;
import object.ReturnBool;
import tpInterface.IProduct;

/**
 *
 * @author SonTT
 */
public class ProductM implements IProduct {

    private TPConnection connection;

    public ProductM() {
    }

    public static Iterator getProducts(String test) {
        String driveString = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://localhost:1433; databasename=tech-pro";
        String user = "sa";
        String pass = "fat123456";

        Vector<ProductDetail> v = new Vector<ProductDetail>();
        try {
            Class.forName(driveString);
            Connection conn = DriverManager.getConnection(url, user, pass);
            Statement stm = conn.createStatement();
            String query = "SELECT"
                    + " Product.ProductID,"
                    + " Product.ProductName,"
                    + " Producer.ProducerName,"
                    + " Category.CategoryName,"
                    + " Image.Path,"
                    + " Product.Description,"
                    + " Product.Price,"
                    + " Product.Quantity,"
                    + " Product.Activated,"
                    + " Product.Deleted,"
                    + " Product.ModifiedDate"
                    + " FROM Product"
                    + " INNER JOIN Producer ON Product.ProducerID = Producer.ProducerID"
                    + " INNER JOIN Category ON Product.CategoryID = Category.CategoryID"
                    + " INNER JOIN Image ON Product.ImageID = Image.ImageID"
                    + " WHERE Product.Deleted = 0";

            ResultSet rs = stm.executeQuery(query);

            if (rs.wasNull()) {
                return null;
            } else {
                while (rs.next()) {
                    ProductDetail pd = new ProductDetail(
                            rs.getInt("ProductID"),
                            rs.getNString("ProductName"),
                            rs.getNString("ProducerName"),
                            rs.getNString("CategoryName"),
                            rs.getNString("Path"),
                            rs.getNString("Description"),
                            rs.getFloat("Price"),
                            rs.getInt("Quantity"),
                            rs.getBoolean("Activated"),
                            rs.getBoolean("Deleted"),
                            rs.getDate("ModifiedDate"));
                    v.addElement(pd);
                }

                return v.iterator();
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public List<ProductDetail> getProducts() {
        List<ProductDetail> products = new ArrayList<ProductDetail>();

        try {
            connection = new TPConnection();
            Statement stm = connection.getConnection().createStatement();
            String query = "SELECT"
                    + " Product.ProductID,"
                    + " Product.ProductName,"
                    + " Producer.ProducerName,"
                    + " Category.CategoryName,"
                    + " Image.Path,"
                    + " Product.Description,"
                    + " Product.Price,"
                    + " Product.Quantity,"
                    + " Product.Activated,"
                    + " Product.Deleted,"
                    + " Product.ModifiedDate"
                    + " FROM Product"
                    + " INNER JOIN Producer ON Product.ProducerID = Producer.ProducerID"
                    + " INNER JOIN Category ON Product.CategoryID = Category.CategoryID"
                    + " INNER JOIN Image ON Product.ImageID = Image.ImageID"
                    + " WHERE Product.Deleted = 0";

            ResultSet rs = stm.executeQuery(query);

            if (rs.wasNull()) {
                return null;
            } else {
                while (rs.next()) {
                    ProductDetail p = new ProductDetail(
                            rs.getInt("ProductID"),
                            rs.getNString("ProductName"),
                            rs.getNString("ProducerName"),
                            rs.getNString("CategoryName"),
                            rs.getNString("Path"),
                            rs.getNString("Description"),
                            rs.getFloat("Price"),
                            rs.getInt("Quantity"),
                            rs.getBoolean("Activated"),
                            rs.getBoolean("Deleted"),
                            rs.getDate("ModifiedDate"));
                    products.add(p);
                }

                return products;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public ReturnBool addProduct(Product product) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ReturnBool editProduct(Product product) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ReturnBool deleteProduct(String productID) {
        try {
            connection = new TPConnection();

            Statement stm = connection.getConnection().createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String sql = "UPDATE Product SET Deleted=1 WHERE ProductID=" + productID;
            stm.execute(sql);

            return new ReturnBool(true, "Deleted!");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return new ReturnBool(false, ex.getMessage());
        } catch (SQLException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return new ReturnBool(false, ex.getMessage());
        }
    }

    @Override
    public ReturnBool deleteProducts(String[] productIDs) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<ProductDetail> searchProduct(String keyword) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    private static final Logger LOG = Logger.getLogger(ProductM.class.getName());

    @Override
    public ProductDetail getProductDetailByProductID(String productID) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<ProductDetail> getProductDetailByCategoryID(String categoryID) {
        List<ProductDetail> products = new ArrayList<ProductDetail>();

        try {
            connection = new TPConnection();
            Statement stm = connection.getConnection().createStatement();
            String query = "SELECT"
                    + " Product.ProductID,"
                    + " Product.ProductName,"
                    + " Producer.ProducerName,"
                    + " Category.CategoryName,"
                    + " Image.Path,"
                    + " Product.Description,"
                    + " Product.Price,"
                    + " Product.Quantity,"
                    + " Product.Activated,"
                    + " Product.Deleted,"
                    + " Product.ModifiedDate"
                    + " FROM Product"
                    + " INNER JOIN Producer ON Product.ProducerID = Producer.ProducerID"
                    + " INNER JOIN Category ON Product.CategoryID = Category.CategoryID"
                    + " INNER JOIN Image ON Product.ImageID = Image.ImageID"
                    + " WHERE Product.Deleted = 0 AND Category.CategoryID=" + categoryID;

            ResultSet rs = stm.executeQuery(query);

            if (rs.wasNull()) {
                return null;
            } else {
                while (rs.next()) {
                    ProductDetail p = new ProductDetail(
                            rs.getInt("ProductID"),
                            rs.getNString("ProductName"),
                            rs.getNString("ProducerName"),
                            rs.getNString("CategoryName"),
                            rs.getNString("Path"),
                            rs.getNString("Description"),
                            rs.getFloat("Price"),
                            rs.getInt("Quantity"),
                            rs.getBoolean("Activated"),
                            rs.getBoolean("Deleted"),
                            rs.getDate("ModifiedDate"));
                    products.add(p);
                }

                return products;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(ProductM.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}
